![Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(3).ソートキーの恩恵を受けられないクエリ](https://devio2023-media.developers.io/wp-content/uploads/2014/05/Amazon_Redshift.png)
Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(3).ソートキーの恩恵を受けられないクエリ
当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第3弾です。課題#3の『ソートキーの恩恵を受けられないクエリ』について内容を見て行きたいと思います。
『課題 #3: ソートキーの恩恵を受けられないクエリ』を実践してみる
この項では、ソートキーの指定を行っていないテーブルを洗い出し、適切なソートキーを設定した方が良いよ、という内容について言及しています。適切なソートキーの選択については以下公式ドキュメントの内容をご参照ください。
また、少し前に新しいソートキーの指定方法として『Interleaved Sorting』というものが利用出来るようになっています。利用・活用シーンに合わせて適切な指定を行なうようにしてください。
- Amazon Web Services ブログ: 【AWS発表】Interleaved Sorting機能で Amazon Redshiftの効果的な検索フィルタリングが可能に
- 【新機能】Amazon Redshift の Interleaved Sorting機能を試してみた | Developers.IO
- Amazon Redshift Interleaved Sortkey と VACUUM 実行のポイント | Developers.IO
- Redshift の Interleaved Sort Key について解説してみる - Qiita
以下のSQLでは、利用回数が多い、且つソートキーの指定が無い(t.sortkey1がNULLである=ソートキー指定されている項目(の1つ目)が存在しない、即ちソートキー指定がされていない)データについて、テーブル毎にその情報を表示しています。
# SELECT database, table_id, schema || '.' || "table" AS "table", size, nvl(s.num_qs,0) num_qs FROM svv_table_info t LEFT JOIN (SELECT tbl, COUNT(distinct query) num_qs FROM stl_scan s WHERE s.userid > 1 AND s.perm_table_name NOT IN ('Internal Worktable','S3') GROUP BY tbl ) s ON s.tbl = t.table_id WHERE t.sortkey1 IS NULL ORDER BY 5 desc; database | table_id | table | size | num_qs -------------+----------+----------------------------------------+-------+-------- xxxxxxxxxxx | 280226 | zzzzzzz.m_user | 36 | 777 xxxxxxxxxxx | 313692 | aaaaaa.test1 | 15 | 555 xxxxxxxxxxx | 108359 | bbbbbbbbbbbb.xxxxxxxxxx_log | 14 | 123 xxxxxxxxxxx | 108977 | zzzzzzz.orders | 96 | 10 : xxxxxxxxxxx | 253188 | public.test_table | 15 | 0 (xx rows)
上記SQLで参照しているテーブルを個別に確認してみると、確かにsortkey1の値には何も入っていません。まずそもそもの『ソートキー』を設定する事から始めようよ、という事ですね。
# SELECT * FROM svv_table_info WHERE schema = 'zzzzzzz' AND svv_table_info.table = 'm_user'; database | schema | table_id | table | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows -------------+---------+----------+--------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+----------- xxxxxxxxxxx | zzzzzzz | 280226 | m_user | Y | EVEN | | 50 | | 0 | 36 | 0.0094 | 0 | | 100.00 | 145 | | (1 row)
件数をカウントしているテーブルはSTL_SCANというテーブルになります。こちらをテーブルID単独で指定して内容を見てみると、以下の様な内容でどの様なSQLがそのテーブルに対して発行されていたかの情報を確認する事が出来ます。
SELECT s.tbl, s.query, TRIM(q.querytxt) AS querytxt FROM stl_scan s INNER JOIN stl_query q ON (s.query = q.query) WHERE s.tbl = '280226' tbl | query | querytxt --------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 280226 | 603814 | fetch 10000 in "SQL_CUR0x7f89c431dfd0" 280226 | 608402 | SELECT COUNT(*) FROM zzzzzzz.m_user; 280226 | 601699 | fetch 10000 in "SQL_CUR0x7fede16297c0" 280226 | 601699 | fetch 10000 in "SQL_CUR0x7fede16297c0" : 280226 | 604742 | fetch 10000 in "SQL_CUR0x7f8122fa11a0" 280226 | 608149 | SELECT (中略) FROM zzzzzzz.aaaaaaaaaa INNER JOIN zzzzzzz.m_user ON (後略);
そして関連するGitHubに登録されている以下のSQL。テーブルに対してどの様なフィルタリング条件が設定されていたか等の情報を確認する事が出来ます。この辺りで得られた情報を元に、実際にどの様なソートキーを定めて行くかを決めて行く、という流れですね。
/********************************************************************************************** Purpose: Return instances of table filter for all or a given table in the past 7 days Columns: table: Table Name filter: Text of the filter from explain plan secs: Number of seconds spend scaning the table num: Number of times that filter occured query: Latest query id of a query that used that filter on that table Notes: Use the perm_table_name fileter to narrow the results History: 2015-02-09 ericfe created 2015-11-20 ericfe filter off nodeid 0 rows and non proper filter plan info **********************************************************************************************/ select trim(s.perm_Table_name) as table , substring(trim(info),1,580) as filter, sum(datediff(seconds,starttime,case when starttime > endtime then starttime else endtime end)) as secs, count(distinct i.query) as num, max(i.query) as query from stl_explain p join stl_plan_info i on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid ) join stl_scan s on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step) where s.starttime > dateadd(day, -7, current_Date) and s.perm_table_name not like 'Internal Worktable%' and p.info like 'Filter:%' and p.nodeid > 0 and s.perm_table_name like '%m_user%' -- choose table(s) to look for group by 1,2 order by 1, 3 desc , 4 desc;
まとめ
以上、『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』トピック3つめ、"ソートキーの恩恵を受けられないクエリ"に関する対処方法のご紹介でした。4つ目以降のトピックについても、こんな感じで読み解きつつ実践して行きたいと思います。